package com.guagua.chat.active;
/**
 * 北京八度互联科技有限公司版权所有
 * Copyright (C) Badu Corporation. All Rights Reserved
 */
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.cassandra.thrift.Cassandra.system_add_column_family_args;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.guagua.base.database.ConnectionManager;
import com.guagua.chat.base.BaseAction;
import com.guagua.chat.base.BaseDao;
import com.guagua.chat.base.PaginationInfo;
import com.guagua.chat.room.ChatRoomUtil;
import com.guagua.chat.room.TBroadcast;
import com.guagua.vas.constants.ConfigConstants;
import com.guagua.vas.util.DateUtil;
/** ******************************************************************
* 类概要:活动操作类 ，对表t_room_active的操作
 * @author 白云鹏
 * @version 2012年7月31日10:11:39
*******************************************************************/
public class ActiveDao extends BaseDao{
//	protected final static Logger logger = LoggerFactory.getLogger(ActiveDao.class);
	/**
	 * 函数说明：根据房间ID获取所有活动 <br/>
	 * 
     * @param room_id
	 *            类型:(long)；<br/> 房间ID<br/>；
	 * @param type
	 *            类型:(int)；<br/> 查询类型，主要分两类，当为1时，查询所有还未结束的活动，按照开始时间升序排序。
	 *            其他类型是查询所有活动，按照结束时间降序排序<br/>；
	 * @return 返回结果说明. （List）活动集合
	 */
	public List getActive(long room_id,int type){
		return getActive(room_id,type,5);
	}
	/**
	 * 
	 * @param qz 前缀
	 * @return
	 */
	private String getOrderBySql(String qz,boolean isOrderBy,String ascOrDesc){
		StringBuilder sql = new StringBuilder();
		if(isOrderBy){
			sql.append(" order by ");
		}
		long currTime = System.currentTimeMillis();
//		long currTime = Long.parseLong("9999999999999");
		sql.append(" case when "+currTime+">"+qz+"end_time then  ")
//		.append(" (select ("+currTime+"-a.end_time) from t_room_active) ")
		.append("9999999999999-a.end_time ")
		//.append(" when dbo.fn_datetime_to_timestamp(getdate())>"+qz+"start_time then  ")
		//.append(" "+qz+"end_time ")
		.append(" else  ")
		.append(" "+qz+"start_time  ")
		.append(" end ")
//		sql.append(" "+qz+"start_time  ")
		.append(ascOrDesc);
		return sql.toString();
	}
	
	public List getActiveById(long activeId){
		List actives = new ArrayList();
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			Date date=new Date();
			String sql="select a.id,a.room_id,a.active_subject,a.active_content,a.join_amount,a.image,a.start_time,a.end_time,a.creator,a.audit_time,a.auditor,a.reject_reason,a.status,p.photo_path,p.id as photoid,p.photo_name,a.attachment,f.photo_path as fileURL,f.id as fileid,f.photo_name as filename ," +
				" (select count(1) from t_active_center with(nolock) where active_id=a.id) center_flag from t_room_active a with(nolock) left join t_photo p with(nolock) on a.image=p.id left join t_photo f with(nolock) on a.attachment=f.id  left join t_broadcast b with(nolock)" +
				" on a.id=b.active_id where a.id=?";
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, activeId);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Map map = new HashMap();
				map.put("id", rs.getLong("id"));
				map.put("room_id", rs.getLong("room_id"));
				map.put("active_subject", rs.getString("active_subject"));
				map.put("active_content_len", ChatRoomUtil.returnStrByLen(rs.getString("active_content"),60));
				map.put("active_content", rs.getString("active_content"));
				map.put("join_amount", rs.getLong("join_amount"));
				map.put("image", rs.getLong("image"));
				map.put("start_time", DateUtil.long2date((Long)rs.getLong("start_time"),"yyyy-MM-dd HH:mm"));
				map.put("end_time", DateUtil.long2date(rs.getLong("end_time"),"yyyy-MM-dd HH:mm"));
				map.put("start_time_long", (Long)rs.getLong("start_time"));
				map.put("end_time_long", rs.getLong("end_time"));
				map.put("creator", rs.getLong("creator"));
				map.put("audit_time", rs.getLong("audit_time"));
				map.put("auditor", rs.getLong("auditor"));
				map.put("reject_reson", rs.getString("reject_reason"));
				map.put("status", rs.getInt("status"));
				map.put("imgURL", rs.getString("photo_path")==null?"":ConfigConstants.IMG_URL+rs.getString("photo_path"));
				map.put("photoid", rs.getLong("photoid"));
				map.put("photo_name", rs.getString("photo_name"));
				map.put("center_flag", rs.getInt("center_flag"));
				map.put("attachment", rs.getInt("attachment"));
				//附件
				map.put("fileURL", rs.getString("fileURL"));
				long fileid =  rs.getLong("fileid");
				map.put("fileid",fileid==0?-1:fileid);
				map.put("filename", rs.getString("filename"));
				
				actives.add(map);
				
			}
		}catch(Exception sqle){
			sqle.printStackTrace();
		}finally{
			ConnectionManager.close(rs, pstmt, con);
		}
		
		
		return actives;
	}
	
	public List getActive(long room_id,int type,int pageSize){
		List actives = new ArrayList();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			String sql="";
			if (type==1){
				Date date=new Date();
				sql="select top "+pageSize+"  a.id,a.room_id,a.active_subject,a.active_content,a.join_amount,a.image,a.start_time,a.end_time,a.creator,a.audit_time,a.auditor,a.reject_reason,a.status,p.photo_path,p.id as photoid,p.photo_name,a.attachment,f.photo_path as fileURL,f.id as fileid,f.photo_name as filename," +
				" (select count(1) from t_active_center with(nolock) where active_id=a.id) center_flag from t_room_active a with(nolock) left join t_photo p with(nolock) on a.image=p.id left join t_photo f with(nolock) on a.attachment=f.id left join t_broadcast b with(nolock)" +
				" on a.id=b.active_id and b.des_room_id="+room_id+" where (a.room_id=? or b.des_room_id="+room_id+") "
				+"and a.status<>3 and a.end_time>="+date.getTime()
				+" order by abs("+date.getTime()+"-a.start_time)";
			}else{
				sql="select top "+pageSize+" a.id,a.room_id,a.active_subject,a.active_content,a.join_amount,a.image,a.start_time,a.end_time,a.creator,a.audit_time,a.auditor,a.reject_reason,a.status,p.photo_path,p.id as photoid,p.photo_name,a.attachment,f.photo_path as fileURL,f.id as fileid,f.photo_name as filename," +
				" (select count(1) from t_active_center with(nolock) where active_id=a.id) center_flag from t_room_active a with(nolock) left join t_photo p with(nolock) on a.image=p.id left join t_photo f with(nolock) on a.attachment=f.id left join t_broadcast b with(nolock)"+
				"  on a.id=b.active_id where (a.room_id="+room_id+" or b.des_room_id=?) and a.status<>3 "+
//				"order by a.end_time desc";
				"order by a.start_time desc";
			}
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, room_id);
			
			rs = pstmt.executeQuery();
			while(rs.next()){
				Map map = new HashMap();
				map.put("id", rs.getLong("id"));
				map.put("room_id", rs.getLong("room_id"));
				map.put("active_subject", rs.getString("active_subject"));
				map.put("active_content_len", ChatRoomUtil.returnStrByLen(rs.getString("active_content"),60));
				map.put("active_content", rs.getString("active_content"));
				map.put("join_amount", rs.getLong("join_amount"));
				map.put("image", rs.getLong("image"));
				map.put("start_time", DateUtil.long2date((Long)rs.getLong("start_time"),"yyyy-MM-dd HH:mm"));
				map.put("end_time", DateUtil.long2date(rs.getLong("end_time"),"yyyy-MM-dd HH:mm"));
				map.put("start_time_long", (Long)rs.getLong("start_time"));
				map.put("end_time_long", rs.getLong("end_time"));
				map.put("creator", rs.getLong("creator"));
				map.put("audit_time", rs.getLong("audit_time"));
				map.put("auditor", rs.getLong("auditor"));
				map.put("reject_reson", rs.getString("reject_reason"));
				map.put("status", rs.getInt("status"));
				map.put("imgURL", rs.getString("photo_path")==null?"":ConfigConstants.IMG_URL+rs.getString("photo_path"));
				map.put("photoid", rs.getLong("photoid"));
				map.put("photo_name", rs.getString("photo_name"));
				map.put("center_flag", rs.getInt("center_flag"));
				map.put("attachment", rs.getInt("attachment"));
				//附件
				map.put("fileURL", rs.getString("fileURL"));
				map.put("fileid", rs.getLong("fileid"));
				map.put("filename", rs.getString("filename"));
				
				actives.add(map);
				
			}
		}catch(Exception sqle){
			sqle.printStackTrace();
		}finally{
			ConnectionManager.close(rs, pstmt, con);
		}
		
		
		return actives;
	}
	
	
	public Object[] listActive(int currentPage,long room_id,int pageSize){
		Object[] arr = new Object[2];
		List actives = new ArrayList();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String jsonPageInfo="";
		try{
			String sql="";
			Date date=new Date();
//			if (type==1){
//				sql="select a.id,a.room_id,a.active_subject,a.active_content,a.join_amount,a.image,a.start_time,a.end_time,a.creator,a.audit_time,a.auditor,a.reject_reason,a.status,p.photo_path,p.id as photoid,p.photo_name," +
//				" (select count(1) from t_active_center with(nolock) where active_id=a.id) center_flag from t_room_active a with(nolock) left join t_photo p with(nolock) on a.image=p.id left join t_broadcast b with(nolock)" +
//				" on a.id=b.active_id and b.des_room_id="+room_id+" where (a.room_id="+room_id+" or b.des_room_id="+room_id+") "
//				+"and a.status<>3 and a.end_time>="+date.getTime();
//			}else{
				sql="select a.id,a.room_id,a.active_subject,a.active_content,a.join_amount,a.image,a.start_time,a.end_time,a.creator,a.audit_time,a.auditor,a.reject_reason,a.status,p.photo_path,p.id as photoid,p.photo_name,a.attachment,f.photo_path as fileURL,f.id as fileid,f.photo_name as filename," +
				" (select count(1) from t_active_center with(nolock) where active_id=a.id) center_flag from t_room_active a with(nolock) left join t_photo p with(nolock) on a.image=p.id left join t_photo f with(nolock) on a.attachment=f.id left join t_broadcast b with(nolock)"+
				" on a.id=b.active_id and b.des_room_id="+room_id+" where (a.room_id="+room_id+" or b.des_room_id="+room_id+") "
				+"and a.status<>3 ";
				
				
//			}
			PaginationInfo pi = this.getPagination(sql,currentPage,pageSize);
			
	        jsonPageInfo = this.getPagingJsonStr(pi);
	        String sql_ = "";
//	        if (type==1){
//	        	sql_= this.genPagingSql(pi,sql,"abs("+date.getTime()+"-a.start_time)");
//				//sql+= " order by abs("+date.getTime()+"-a.start_time)";
//			}else{
			sql_= this.genPagingSql(pi,sql,getOrderBySql("a.",false," asc "));
//			sql_= this.genPagingSql(pi,sql,"a.end_time desc");
				//sql+= " order by a.end_time desc";
//			}
	        
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			pstmt = con.prepareStatement(sql_);
//			pstmt.setLong(1, room_id);
			
			rs = pstmt.executeQuery();
			while(rs.next()){
				Map map = new HashMap();
				map.put("id", rs.getLong("id"));
				map.put("room_id", rs.getLong("room_id"));
				map.put("active_subject", rs.getString("active_subject"));
//				map.put("active_subject_len", rs.getString("active_subject"));
				map.put("active_content_len", ChatRoomUtil.returnStrByLen( rs.getString("active_content"),60));
				map.put("active_content", rs.getString("active_content"));
//				map.put("active_content", rs.getString("active_content"));
				map.put("join_amount", rs.getLong("join_amount"));
				map.put("image", rs.getLong("image"));
				map.put("start_time", DateUtil.long2date((Long)rs.getLong("start_time"),"yyyy-MM-dd HH:mm"));
				map.put("end_time", DateUtil.long2date(rs.getLong("end_time"),"yyyy-MM-dd HH:mm"));
				map.put("start_time_long", (Long)rs.getLong("start_time"));
				map.put("end_time_long", rs.getLong("end_time"));
				map.put("creator", rs.getLong("creator"));
				map.put("audit_time", rs.getLong("audit_time"));
				map.put("auditor", rs.getLong("auditor"));
				map.put("reject_reson", rs.getString("reject_reason"));
				map.put("status", rs.getInt("status"));
				map.put("imgURL", rs.getString("photo_path")==null?"":ConfigConstants.IMG_URL+rs.getString("photo_path"));
				map.put("photoid", rs.getLong("photoid"));
				map.put("photo_name", rs.getString("photo_name"));
				map.put("center_flag", rs.getInt("center_flag"));
				map.put("attachment", rs.getInt("attachment"));
				//附件
				map.put("fileURL", rs.getString("fileURL"));
				map.put("fileid", rs.getLong("fileid"));
				map.put("filename", rs.getString("filename"));
				
				actives.add(map);
				
			}
		}catch(Exception sqle){
			sqle.printStackTrace();
		}finally{
			ConnectionManager.close(rs, pstmt, con);
		}

		arr[0] = actives;
		arr[1] = jsonPageInfo;
		return arr;
	}
	
	
	/**
	 * 函数说明：添加转播 <br/>
	 * 
     * @param active_id
	 *            类型:(long)；<br/> 活动id<br/>；
	 * @return 返回结果说明. （boolean）是否操作成功，true为成功，false为失败
	 */
	public boolean addActiveToMyRoom(long srcRoomId,long activeId,long desRoomId,long desUid){
		boolean flag=false;
		Connection con = null;
		PreparedStatement pstmt = null;
		try{
			String sql="insert into t_broadcast(src_room_id,active_id,des_room_id,broadcastor,create_time)" +
					"values(?,?,?,?,dbo.fn_datetime_to_timestamp(getdate()))";
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, srcRoomId);
			pstmt.setLong(2, activeId);
			pstmt.setLong(3, desRoomId);
			pstmt.setLong(4, desUid);
			
		    pstmt.execute();
		    flag=true;
		}catch(Exception sqle){
			sqle.printStackTrace();
		}finally{
			ConnectionManager.close(pstmt, con);
		}
		return flag;
	}
	
	/**
	 * 函数说明：添加活动 <br/>
	 * 
     * @param active
	 *            类型:(Active)；<br/> 活动对象<br/>；
	 * @return 返回结果说明. （boolean）是否操作成功，true为成功，false为失败
	 */
	public long addActive(Active active){
		long flag=0;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			String sql="insert into t_room_active(room_id,active_subject,active_content,join_amount,image,start_time,end_time,create_time,status,creator,tag_catalog_id,tag_id,room_catalog,last_operate,attachment)" +
					"values(?,?,?,0,?,?,?,dbo.fn_datetime_to_timestamp(getdate()),0,?,?,?,?,1,?);SELECT SCOPE_IDENTITY() AS id";
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, active.getRoom_id());
			pstmt.setString(2, active.getActive_subject());
			pstmt.setString(3,active.getActive_content());
			pstmt.setLong(4,active.getImage());
			pstmt.setLong(5,active.getStart_time());
			pstmt.setLong(6,active.getEnd_time());
			pstmt.setLong(7,active.getCreator());
			pstmt.setInt(8,active.getTag_catalog_id());
			pstmt.setInt(9,active.getTag_id());
			pstmt.setInt(10,active.getRoom_catalog());
			pstmt.setLong(11,active.getAttachment());
			
			rs=pstmt.executeQuery();
			if (rs.next()){
				 flag=rs.getLong("id");
			}
		   
		}catch(Exception sqle){
			sqle.printStackTrace();
		}finally{
			ConnectionManager.close(rs,pstmt, con);
		}
		return flag;
	}
	/**
	 * 函数说明：添加活动到活动中心 <br/>
	 * 
     * @param active_id
	 *            类型:(long)；<br/> 活动id<br/>；
	 * @return 返回结果说明. （boolean）是否操作成功，true为成功，false为失败
	 */
	public boolean toActive(long active_id){
		boolean flag=false;
		Connection con = null;
		PreparedStatement pstmt = null;
		try{
			String sql="insert into t_active_center(active_id,create_time)" +
					"values(?,dbo.fn_datetime_to_timestamp(getdate()))";
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, active_id);
			
		    pstmt.execute();
		    flag=true;
		}catch(Exception sqle){
			sqle.printStackTrace();
		}finally{
			ConnectionManager.close(pstmt, con);
		}
		return flag;
	}
	/**
	 * 函数说明：更新活动 <br/>
	 * 
     * @param active
	 *            类型:(Active)；<br/> 活动对象<br/>；
	 * @return 返回结果说明. （boolean）是否操作成功，true为成功，false为失败
	 */
	public boolean updateActive(Active active){
		boolean flag=false;
		Connection con = null;
		PreparedStatement pstmt = null;
		try{
			String sql="update t_room_active set active_subject=?,active_content=?,image=?,last_update=dbo.fn_datetime_to_timestamp(getdate()),start_time="+active.getStart_time()+",end_time="+active.getEnd_time()+",last_operate=2,attachment=? where id=?";
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, active.getActive_subject());
			pstmt.setString(2, active.getActive_content());
			pstmt.setLong(3, active.getImage());
			pstmt.setLong(4, active.getAttachment());
			pstmt.setLong(5, active.getId());
			
		    pstmt.execute();
		    flag=true;
		}catch(Exception sqle){
			sqle.printStackTrace();
		}finally{
			ConnectionManager.close(pstmt, con);
		}
		return flag;
	}
	/**
	 * 函数说明：删除活动 <br/>
	 * 
     * @param active
	 *            类型:(Active)；<br/> 活动对象<br/>；
	 * @return 返回结果说明. （boolean）是否操作成功，true为成功，false为失败
	 */
	public boolean delActive(Active active){
		boolean flag=false;
		Connection con = null;
		Statement stmt = null;
		try{
			String[] sql={"update t_active_join_user set status=0 where active_id="+active.getId(),
			              "update t_room_active  set status=3 where id="+active.getId()};
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			stmt=con.createStatement();
			for (String s:sql){
				stmt.addBatch(s);
			}
			
		    stmt.executeBatch();
		    flag=true;
		}catch(Exception sqle){
			sqle.printStackTrace();
		}finally{
			ConnectionManager.close(stmt, con);
		}
		return flag;
	}
	/**
	 * 函数说明：添加活动报名者 <br/>
	 * 
     * @param active
	 *            类型:(Active)；<br/> 活动对象<br/>；
	 * @return 返回结果说明. （boolean）是否操作成功，true为成功，false为失败
	 */
	public boolean addJoiner(Active active){
		
		Connection con = null;
		Statement  stmt = null;

		String sql1="";
		String sql2="";
		
		long currTime = System.currentTimeMillis();
		
		try {
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			con.setAutoCommit(false); 
			stmt = con.createStatement();
			
			sql1="insert into t_active_join_user (active_id,user_id,room_id,create_time,status) " +
				"values ("+active.getId()+","+active.getUser_id()+","+active.getRoom_id()+","+currTime+",1)";
			sql2="update t_room_active set join_amount=join_amount+1 where id="+active.getId();

//			logger.debug("====================================================\n");
//			logger.debug("添加报名SQL1:"+sql1+"\n");
//			logger.debug("添加报名SQL2："+sql2+"\n");
//			logger.debug("====================================================\n");
			stmt.addBatch(sql1);
			stmt.addBatch(sql2);
			
			stmt.executeBatch();    //执行批处理 
			con.commit(); 
//			System.out.println("sql1:-------------------"+sql1);
//			System.out.println("sql2:-------------------"+sql2);
		}catch (BatchUpdateException sqle) {
			try {
				con.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return false;
			
		}catch(Exception sqle){
//			logger.debug("====================================================\n");
//			logger.debug("报名失败sqle\n");
//			logger.debug("====================================================\n");
			try {
				con.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return false;
		}finally {
			ConnectionManager.close(stmt, con);
		}
		
		
		return true;
  }
	/**
	 * 函数说明：检查访问用户是否参加过报名 <br/>
	 * 
     * @param active
	 *            类型:(Active)；<br/> 活动对象<br/>；
	 * @param user_id 
	 * @return 返回结果说明. （boolean）是否操作成功，true为没有报名，false为已经报名，不能再参加
	 */
	public boolean checkJoiner(Active active){
		boolean flag=true;
		Connection con = null;
		Statement  stmt = null;
		ResultSet rs = null;
		try {
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			stmt = con.createStatement();
			String sql="select 1 from t_active_join_user with(nolock) where active_id="+active.getId()+" and user_id="+active.getUser_id()+" and room_id="+active.getRoom_id();
			rs = stmt.executeQuery(sql);
			if(rs.next()) {
				flag=false;
			}
//			logger.debug("====================================================\n");
//			logger.debug("当前报名状态flag:"+flag+"(true没报名，false报名)"+"\n");
//			logger.debug("查询报名的SQL："+sql+"\n");
//			logger.debug("====================================================\n");
		}catch(Exception sqle){
			sqle.printStackTrace();
		}finally {
			ConnectionManager.close(rs,stmt, con);
		}
		
		return flag;
		
	}
	
	/**
	 * 增加转播记录
	 * @param br
	 */
	public void saveBroadcast(TBroadcast br){
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			stmt = con.prepareStatement("delete from t_broadcast where src_room_id="+ br.getSrc_room_id() +" and active_id="+ br.getActive_id() +" and des_room_id=" + br.getDes_room_id() + " and broadcastor=" + br.getBroadcastor());
			stmt.executeUpdate();
			stmt = con.prepareStatement("insert into t_broadcast (src_room_id, active_id, des_room_id, broadcastor, create_time) values (?, ?, ?, ? ,?)");
			stmt.setLong(1, br.getSrc_room_id());
			stmt.setLong(2, br.getActive_id());
			stmt.setLong(3, br.getDes_room_id());
			stmt.setLong(4, br.getBroadcastor());
			stmt.setLong(5, new Date().getTime());
			stmt.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(rs,stmt, con);
		}
	}
	/**
	 * 获取用户的转播记录
	 * @param userId
	 * @return
	 */
	public List<TBroadcast> getBroadcastList(long userId,long activeid){
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<TBroadcast> list = new ArrayList<TBroadcast>();
		try {
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			stmt = con.prepareStatement("SELECT id, src_room_id, active_id, des_room_id, broadcastor, create_time FROM t_broadcast with(NOLOCK) WHERE broadcastor="+ userId +" and active_id="+activeid+" order by id asc");
			rs = stmt.executeQuery();
			while(rs.next()){
				TBroadcast br = new TBroadcast();
				br.setId(rs.getLong("id"));
				br.setSrc_room_id(rs.getLong("src_room_id"));
				br.setActive_id(rs.getLong("active_id"));
				br.setDes_room_id(rs.getInt("des_room_id"));
				br.setBroadcastor(rs.getLong("broadcastor"));
				br.setCreate_time(rs.getLong("create_time"));
				list.add(br);
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(rs,stmt, con);
		}
		return list;
	}
	
	public boolean delZbActive(long activeid,long srcRoomid,long desRoomid,long uid){
		boolean flag = false;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			stmt = con.prepareStatement("delete from t_broadcast where src_room_id="+ srcRoomid +" and active_id="+ activeid +" and des_room_id=" + desRoomid+ " and broadcastor=" + uid);
			stmt.executeUpdate();
		    flag=true;
		}catch (Exception e) {
			e.printStackTrace();
			flag = false;
		} finally {
			ConnectionManager.close(rs,stmt, con);
		}
		return flag;
	}
	/**
	 * 函数说明：获取正在运行的活动数量 <br/>
	 * 
	 * @return 返回结果正在活动的数量
	 */
	public int getActiveStartCount(Active active){
		int flag=0;
		Connection con = null;
		Statement  stmt = null;
		ResultSet rs = null;
		try {
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			stmt = con.createStatement();
			String sql="select count(1) as activeStarCount from t_room_active with(nolock) where room_id="+active.getRoom_id();
			if(active.getId()>0){
				sql +=" and id <> "+active.getId()+" and ";	
			}else{
				sql +=" and ";
			}
			sql += " (("+active.getStart_time()+"> start_time and "+active.getStart_time()+"  <end_time ) or ";//开始时间在重复期内
			sql += " ("+active.getEnd_time()+"> start_time and "+active.getEnd_time()+"  <end_time ) or  ";//结束时间在重复期内
			sql += " ("+"start_time>="+active.getStart_time()+"  and "+" end_time  <="+active.getEnd_time()+" ))";//开始时间小于数据库中的开始时间，结束时间大于数据库
			rs = stmt.executeQuery(sql);
			if(rs.next()) {
				flag=rs.getInt("activeStarCount");
			}
		}catch(Exception sqle){
			sqle.printStackTrace();
			flag = 0;
		}finally {
			ConnectionManager.close(rs,stmt, con);
		}
		
		return flag;
		
	}
}
